The Translation of Star Schema into Entity-Relationship Diagrams

The star schema is widely accepted as a proper underlying table structure for data warehouses, but enterprise data is frequently defined in terms of entity-relationship diagrams. A key issue for data warehouse designers is how to move from legacy OLTP designs into the star schema. In this paper, we examine the semantics and constraints of the star schema in some of its different forms and translate them into analogous ERDs. These different forms include the simple, multi-star, and snowflaked star schema, and several variations on facts and dimensions such as associative, multple fact and outboard dimension tables. We outline transformation rules for each case and show a series of examples. We believe that these heuristics can be used in mapping and maintaining consistency between OLTP databases and data warehouses.